﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;


namespace WinFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        // 查询所有
        private void btnQuery_Click(object sender, EventArgs e)
        {
            // 连接到指定数据库
            using (var sqlConStr = new SQLiteConnection(@"DataSource=D:\SqLite\DB\info.db;Pooling=true;FailIfMissing=false"))
            {
                try
                {
                    // 打卡数据库
                    sqlConStr.Open();
                    var dataTable = new DataTable();  // 创建一个表格
                    // 执行sql语句，SQLiteDataAdapter对象执行后返回的是table数据集
                    using (var adp = new SQLiteDataAdapter("select * from user", sqlConStr))
                    {  // 执行sql语句
                        adp.Fill(dataTable);  // 将查到的信息填入表格
                    }
                    dataGridView1.DataSource = dataTable;  // 将表格信息填充到控件上
                }
                catch(Exception ex)
                {
                    MessageBox.Show("查询出错" + ex.Message);
                }
            }
        }

        // 添加
        private void button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(textid.Text) || string.IsNullOrWhiteSpace(textName.Text))
            {
                MessageBox.Show("内容为空");
                return;
            }
            using (var sqlConn = new SQLiteConnection(@"DataSource=D:\SqLite\DB\info.db;Pooling=true;FailIfMissing=false"))
            {
                try
                {
                    if (sqlConn.State == ConnectionState.Closed)
                    {
                        sqlConn.Open();// 打开数据库
                    }
                    // 定义sql语句
                    using (var cmd = new SQLiteCommand("insert into user(id, name) values(@id, @name)", sqlConn))
                    {
                        // @作为占位符，对他进行赋值
                        cmd.Parameters.AddWithValue("@id", textid.Text);
                        cmd.Parameters.AddWithValue("@name", textName.Text);

                        // 执行命令
                        cmd.ExecuteNonQuery();
                    }
                    MessageBox.Show("插入成功");
                    textid.Clear();
                    textName.Clear();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("插入错误：" + ex.Message);
                }
            }

        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(textDel.Text))
            {
                MessageBox.Show("id为空");
                return;
            }
            using(var sqlConn = new SQLiteConnection(@"DataSource=D:\SqLite\DB\info.db;Pooling=true;FailIfMissing=false"))
            {
                try
                {
                    sqlConn.Open();
                    using(var cmd = new SQLiteCommand("delete from user where id = @id", sqlConn))
                    {
                        cmd.Parameters.AddWithValue("@id", textDel.Text);
                        cmd.ExecuteNonQuery();
                    }
                    MessageBox.Show("删除成功");
                    textDel.Clear();
                }
                catch(Exception ex)
                {
                    MessageBox.Show("删除错误：" + ex.Message);
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if(string.IsNullOrWhiteSpace(textid.Text) || string.IsNullOrWhiteSpace(textName.Text)){
                MessageBox.Show("输入为空");
                return;
            }
            using(var sqlconn = new SQLiteConnection(@"DataSource=D:\SqLite\DB\info.db;Pooling=true;FailIfMissing=false"))
            {
                // sql语句
                try
                {
                    sqlconn.Open();
                    using (var cmd = new SQLiteCommand("update user set name = @name where id = @id", sqlconn))
                    {
                        cmd.Parameters.AddWithValue("@name", textName.Text);
                        cmd.Parameters.AddWithValue("@id", textid.Text);
                        cmd.ExecuteNonQuery();
                    }
                    MessageBox.Show("修改成功");
                    textid.Clear();
                    textName.Clear();
                }
                catch(Exception ex)
                {
                    MessageBox.Show("修改错误：" + ex.Message);
                }
            }
        }
    }
}
